"""
====================================================================
设备评分DAO层 / Equipment Score Data Access Object
====================================================================
功能说明：
1. 设备评分明细（equipment_scores_detail）的增删查改
2. 项目/区域聚合（equipment_scores_aggregated）的查询和批量写入
3. 周度统计（equipment_weekly_stats）的查询
4. AI报告（analysis_reports）的查询和保存

作者: Data Analysis Team
日期: 2025-11-06
====================================================================
"""

from typing import List, Optional, Dict, Any
from datetime import datetime, timedelta
import pandas as pd
from utils.mongo_util import db
from model.db_schemas import (
    EquipmentScoreDetail,
    EquipmentScoresAggregated,
    EquipmentWeeklyStats,
    AnalysisReport,
)


# ====================================================================
# 设备评分明细操作 (equipment_scores_detail)
# ====================================================================

class EquipmentScoreDAO:
    """设备评分数据访问对象"""

    COLLECTION_NAME = "equipment_scores_detail"

    @classmethod
    def batch_insert_scores(
        cls,
        scores_list: List[Dict[str, Any]]
    ) -> bool:
        """
        批量插入设备评分

        参数:
            scores_list: 评分数据列表

        返回:
            bool: 是否成功
        """
        if not scores_list:
            return True

        try:
            collection = db[cls.COLLECTION_NAME]
            result = collection.insert_many(scores_list, ordered=False)
            print(f"✓ 批量插入{len(result.inserted_ids)}条设备评分")
            return True
        except Exception as e:
            print(f"✗ 批量插入失败: {e}")
            return False

    @classmethod
    def find_by_imei_period(
        cls,
        member_id: int,
        imei: str,
        period_start: datetime,
        period_end: datetime
    ) -> Optional[Dict]:
        """
        查询单个设备在特定时期的评分

        参数:
            member_id: 公司ID
            imei: 设备IMEI
            period_start: 统计周期开始
            period_end: 统计周期结束

        返回:
            评分文档或None
        """
        collection = db[cls.COLLECTION_NAME]
        return collection.find_one({
            "memberId": member_id,
            "imei": imei,
            "period_start": period_start,
            "period_end": period_end
        })

    @classmethod
    def find_by_project(
        cls,
        member_id: int,
        project_name: str,
        period_start: datetime,
        period_end: Optional[datetime] = None
    ) -> List[Dict]:
        """
        查询项目下所有设备的评分

        参数:
            member_id: 公司ID
            project_name: 项目名称
            period_start: 统计周期开始
            period_end: 统计周期结束（可选）

        返回:
            评分文档列表
        """
        collection = db[cls.COLLECTION_NAME]
        query = {
            "memberId": member_id,
            "project_name": project_name,
            "period_start": {"$gte": period_start}
        }
        if period_end:
            query["period_end"] = {"$lte": period_end}

        return list(collection.find(query).sort("period_start", -1))

    @classmethod
    def find_by_region(
        cls,
        member_id: int,
        region_name: str,
        period_start: datetime,
        period_end: Optional[datetime] = None
    ) -> List[Dict]:
        """
        查询区域下所有设备的评分

        参数:
            member_id: 公司ID
            region_name: 区域名称
            period_start: 统计周期开始
            period_end: 统计周期结束（可选）

        返回:
            评分文档列表
        """
        collection = db[cls.COLLECTION_NAME]
        query = {
            "memberId": member_id,
            "region_name": region_name,
            "period_start": {"$gte": period_start}
        }
        if period_end:
            query["period_end"] = {"$lte": period_end}

        return list(collection.find(query).sort("period_start", -1))

    @classmethod
    def find_top_n(
        cls,
        member_id: int,
        n: int = 10,
        order: str = "asc",
        period_start: Optional[datetime] = None,
        dimension: str = "综合分"
    ) -> List[Dict]:
        """
        查询Top N设备

        参数:
            member_id: 公司ID
            n: 返回数量
            order: 排序方向 "asc" (最低) 或 "desc" (最高)
            period_start: 统计周期开始（可选）
            dimension: 评分维度："综合分"|"A_可用性"|"C_出勤覆盖"等

        返回:
            评分文档列表
        """
        collection = db[cls.COLLECTION_NAME]
        query = {"memberId": member_id}
        if period_start:
            query["period_start"] = period_start

        sort_direction = -1 if order == "desc" else 1
        return list(
            collection.find(query)
            .sort(f"scores.{dimension}", sort_direction)
            .limit(n)
        )

    @classmethod
    def find_trend(
        cls,
        member_id: int,
        imei: str,
        months: int = 6
    ) -> List[Dict]:
        """
        查询设备历史趋势（近N个月）

        参数:
            member_id: 公司ID
            imei: 设备IMEI
            months: 月数（默认6个月）

        返回:
            按时间排序的评分文档列表
        """
        collection = db[cls.COLLECTION_NAME]
        start_date = datetime.utcnow() - timedelta(days=30 * months)

        return list(
            collection.find({
                "memberId": member_id,
                "imei": imei,
                "period_start": {"$gte": start_date}
            }).sort("period_start", 1)
        )

    @classmethod
    def count_by_member(cls, member_id: int) -> int:
        """统计指定公司的评分数量"""
        collection = db[cls.COLLECTION_NAME]
        return collection.count_documents({"memberId": member_id})

    @classmethod
    def delete_old_data(
        cls,
        member_id: int,
        before_date: datetime,
        dry_run: bool = True
    ) -> int:
        """
        删除旧数据（可选清理策略）

        参数:
            member_id: 公司ID
            before_date: 删除此日期之前的数据
            dry_run: 是否为模拟运行（不实际删除）

        返回:
            删除的文档数
        """
        collection = db[cls.COLLECTION_NAME]
        query = {
            "memberId": member_id,
            "period_start": {"$lt": before_date}
        }

        if dry_run:
            count = collection.count_documents(query)
            print(f"[模拟运行] 将删除 {count} 条旧数据")
            return count
        else:
            result = collection.delete_many(query)
            print(f"✓ 已删除 {result.deleted_count} 条旧数据")
            return result.deleted_count


# ====================================================================
# 项目/区域聚合操作 (equipment_scores_aggregated)
# ====================================================================

class AggregatedScoreDAO:
    """聚合评分数据访问对象"""

    COLLECTION_NAME = "equipment_scores_aggregated"

    @classmethod
    def batch_insert_aggregated(
        cls,
        aggregated_list: List[Dict[str, Any]]
    ) -> bool:
        """
        批量插入聚合评分

        参数:
            aggregated_list: 聚合评分数据列表

        返回:
            bool: 是否成功
        """
        if not aggregated_list:
            return True

        try:
            collection = db[cls.COLLECTION_NAME]
            result = collection.insert_many(aggregated_list, ordered=False)
            print(f"✓ 批量插入{len(result.inserted_ids)}条聚合评分")
            return True
        except Exception as e:
            print(f"✗ 批量插入失败: {e}")
            return False

    @classmethod
    def find_by_region(
        cls,
        member_id: int,
        period_start: datetime
    ) -> List[Dict]:
        """
        查询区域维度的聚合评分

        参数:
            member_id: 公司ID
            period_start: 统计周期开始

        返回:
            按综合分排序的区域聚合数据
        """
        collection = db[cls.COLLECTION_NAME]
        return list(
            collection.find({
                "memberId": member_id,
                "aggregation_level": "region",
                "period_start": period_start
            }).sort("avg_scores.综合分", -1)
        )

    @classmethod
    def find_by_project(
        cls,
        member_id: int,
        period_start: datetime
    ) -> List[Dict]:
        """
        查询项目维度的聚合评分

        参数:
            member_id: 公司ID
            period_start: 统计周期开始

        返回:
            按综合分排序的项目聚合数据
        """
        collection = db[cls.COLLECTION_NAME]
        return list(
            collection.find({
                "memberId": member_id,
                "aggregation_level": "project",
                "period_start": period_start
            }).sort("avg_scores.综合分", -1)
        )

    @classmethod
    def find_company_level(
        cls,
        member_id: int,
        period_start: datetime
    ) -> Optional[Dict]:
        """
        查询公司级聚合评分

        参数:
            member_id: 公司ID
            period_start: 统计周期开始

        返回:
            公司级聚合数据
        """
        collection = db[cls.COLLECTION_NAME]
        return collection.find_one({
            "memberId": member_id,
            "aggregation_level": "company",
            "period_start": period_start
        })


# ====================================================================
# 周度统计操作 (equipment_weekly_stats)
# ====================================================================

class WeeklyStatsDAO:
    """周度统计数据访问对象"""

    COLLECTION_NAME = "equipment_weekly_stats"

    @classmethod
    def batch_insert_weekly_stats(
        cls,
        stats_list: List[Dict[str, Any]]
    ) -> bool:
        """
        批量插入周度统计

        参数:
            stats_list: 周度统计数据列表

        返回:
            bool: 是否成功
        """
        if not stats_list:
            return True

        try:
            collection = db[cls.COLLECTION_NAME]
            result = collection.insert_many(stats_list, ordered=False)
            print(f"✓ 批量插入{len(result.inserted_ids)}条周度统计")
            return True
        except Exception as e:
            print(f"✗ 批量插入失败: {e}")
            return False

    @classmethod
    def find_by_imei(
        cls,
        member_id: int,
        imei: str,
        weeks: int = 12
    ) -> List[Dict]:
        """
        查询单个设备的周度统计（近N周）

        参数:
            member_id: 公司ID
            imei: 设备IMEI
            weeks: 周数（默认12周）

        返回:
            周度统计列表
        """
        collection = db[cls.COLLECTION_NAME]
        start_date = datetime.utcnow() - timedelta(days=7 * weeks)

        return list(
            collection.find({
                "memberId": member_id,
                "imei": imei,
                "week_start": {"$gte": start_date}
            }).sort("week_start", 1)
        )

    @classmethod
    def find_not_met_weeks(
        cls,
        member_id: int,
        imei: str
    ) -> List[Dict]:
        """
        查询未达标的周数

        参数:
            member_id: 公司ID
            imei: 设备IMEI

        返回:
            未达标周数的统计数据
        """
        collection = db[cls.COLLECTION_NAME]
        return list(
            collection.find({
                "memberId": member_id,
                "imei": imei,
                "stats.is_week_met": 0
            }).sort("week_start", -1)
        )


# ====================================================================
# AI报告操作 (analysis_reports)
# ====================================================================

class AnalysisReportDAO:
    """分析报告数据访问对象"""

    COLLECTION_NAME = "analysis_reports"

    @classmethod
    def insert_report(cls, report_data: Dict[str, Any]) -> bool:
        """
        保存分析报告

        参数:
            report_data: 报告数据

        返回:
            bool: 是否成功
        """
        try:
            collection = db[cls.COLLECTION_NAME]
            result = collection.insert_one(report_data)
            print(f"✓ 报告已保存: {result.inserted_id}")
            return True
        except Exception as e:
            print(f"✗ 报告保存失败: {e}")
            return False

    @classmethod
    def find_latest_report(
        cls,
        member_id: int,
        report_type: str = "equipment_analysis"
    ) -> Optional[Dict]:
        """
        查询最新的分析报告

        参数:
            member_id: 公司ID
            report_type: 报告类型

        返回:
            最新的报告文档
        """
        collection = db[cls.COLLECTION_NAME]
        return collection.find_one(
            {
                "memberId": member_id,
                "report_type": report_type
            },
            sort=[("created_at", -1)]
        )

    @classmethod
    def find_reports_by_period(
        cls,
        member_id: int,
        start_date: datetime,
        end_date: datetime,
        report_type: str = "equipment_analysis"
    ) -> List[Dict]:
        """
        查询时间范围内的报告列表

        参数:
            member_id: 公司ID
            start_date: 开始日期
            end_date: 结束日期
            report_type: 报告类型

        返回:
            报告列表
        """
        collection = db[cls.COLLECTION_NAME]
        return list(
            collection.find({
                "memberId": member_id,
                "report_type": report_type,
                "created_at": {"$gte": start_date, "$lte": end_date}
            }).sort("created_at", -1)
        )

    @classmethod
    def count_expired_reports(
        cls,
        member_id: int
    ) -> int:
        """
        统计过期的报告数量

        参数:
            member_id: 公司ID

        返回:
            过期报告数量
        """
        collection = db[cls.COLLECTION_NAME]
        return collection.count_documents({
            "memberId": member_id,
            "ttl": {"$lt": datetime.utcnow()}
        })


# ====================================================================
# 兼容性：查询旧数据 (equipment_analysis)
# ====================================================================

class LegacyAnalysisDAO:
    """旧的分析数据访问（用于迁移）"""

    COLLECTION_NAME = "equipment_analysis"

    @classmethod
    def find_by_member_and_period(
        cls,
        member_id: int,
        period_start: datetime,
        period_end: datetime
    ) -> Optional[Dict]:
        """
        查询旧格式的分析数据

        参数:
            member_id: 公司ID
            period_start: 统计周期开始
            period_end: 统计周期结束

        返回:
            旧格式的分析数据
        """
        collection = db[cls.COLLECTION_NAME]
        return collection.find_one({
            "memberId": member_id,
            "time_range.start": period_start,
            "time_range.end": period_end
        })

    @classmethod
    def count_legacy_data(cls) -> int:
        """统计旧格式数据的数量"""
        collection = db[cls.COLLECTION_NAME]
        return collection.count_documents({})

    @classmethod
    def get_all_legacy(cls) -> List[Dict]:
        """获取所有旧格式数据（用于迁移）"""
        collection = db[cls.COLLECTION_NAME]
        return list(collection.find({}))

    @classmethod
    def archive_legacy(cls, member_id: int) -> bool:
        """
        归档旧数据

        参数:
            member_id: 公司ID

        返回:
            bool: 是否成功
        """
        try:
            collection = db[cls.COLLECTION_NAME]
            # 可选：复制到归档集合再删除
            result = collection.delete_many({"memberId": member_id})
            print(f"✓ 已归档{result.deleted_count}条旧数据")
            return True
        except Exception as e:
            print(f"✗ 归档失败: {e}")
            return False
